/**
 * 
 */
package com.kunbo.cn.service.impl.core.reporting;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.kunbo.cn.service.core.reporting.SaleReportingService;
import com.kunbo.cn.util.common.Array;
import com.kunbo.cn.util.date.CommonsUtil;
import com.kunbo.cn.util.retu.Pages;

/**
 *
 * @author bjl  
 * @date 2019年4月8日 
 */
@Service
public class SaleReportingServiceImpl implements SaleReportingService {
	@Autowired
	private JdbcTemplate jdbc;
	public List<Map<String, Object>> getSalePoundList(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,Integer page,Integer pageSize) {
		
			String sql1 = "SELECT \r\n" + 
					"	s.* \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			/*if(materiel != null && !materiel.isEmpty()){
				sql1 += " and p.sale_order_code like '%" + sale_order_code + "%'";
			}*/
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'";
			}
			System.out.println("（*************sql1列表**************）"+sql1);
			//sql1 += " and p.state <> 5 and p.state <> 6 " ;
			sql1 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			return list;
	}
	public Pages  getSalePoundList1(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,Integer page,Integer pageSize) {
		
			String sql1 = "SELECT \r\n" + 
					"	s.* \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			/*if(materiel != null && !materiel.isEmpty()){
				sql1 += " and p.sale_order_code like '%" + sale_order_code + "%'";
			}*/
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'";
			}
			System.out.println("（*************sql1列表**************）"+sql1);
			//sql1 += " and p.state <> 5 and p.state <> 6 " ;
			String sql2="";
			sql2 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			  String sql=sql1.concat(sql2);//获取数据sql
			List<Map<String, Object>> list = jdbc.queryForList(sql);
			Pages pages =new Pages();
			  Integer count = jdbc.queryForList(sql1).size();
			  pages.setTotal(count);
			  pages.setData(list);
			return pages;
	}
	public List<Map<String, Object>> getSaleMatterCount(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,Integer page,Integer pageSize) {
		
			String sql1 = "SELECT s.client,s.materiel,COUNT(materiel) AS vehicle_number,"
					+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'";
			}
			sql1+=" group by s.materiel  \r\n";
			sql1 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			return list;
	}
	public Pages getSaleMatterCount1(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,Integer page,Integer pageSize) {
		
			String sql1 = "SELECT s.client,s.materiel,COUNT(materiel) AS vehicle_number,"
					+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'";
			}
			sql1+=" group by s.materiel  \r\n";
			String sql2="";
			sql2 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			  String sql=sql1.concat(sql2);//获取数据sql
			List<Map<String, Object>> list = jdbc.queryForList(sql);
			Pages pages =new Pages();
			  Integer count = jdbc.queryForList(sql1).size();
			  pages.setTotal(count);
			  pages.setData(list);
			return pages;
	}
	public List<Map<String, Object>> getSaleMarginCount(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,Integer page,Integer pageSize) {
		
			String sql1 = "SELECT s.client,s.materiel,s.shipping_unit ,COUNT(client) AS vehicle_number,"
					+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss,n.contr_margin \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s ,(SELECT o.customer,o.contr_margin FROM business_sale_order_manage  o where 1=1 GROUP BY o.customer) as n \r\n" + 
					"WHERE   1 = 1 and s.client=n.customer";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'\r\n";
			}
			sql1+="  group by s.client \r\n";
			sql1 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			return list;
	}
	public Pages getSaleMarginCount1(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,Integer page,Integer pageSize) {
		
			String sql1 = "SELECT s.client,s.materiel,s.shipping_unit ,COUNT(client) AS vehicle_number,"
					+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss,n.contr_margin \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s ,(SELECT o.customer,o.contr_margin FROM business_sale_order_manage  o where 1=1 GROUP BY o.customer) as n \r\n" + 
					"WHERE   1 = 1 and s.client=n.customer";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'\r\n";
			}
			sql1+="  group by s.client \r\n";
			String sql2="";
			sql2 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			  String sql=sql1.concat(sql2);//获取数据sql
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			Pages pages=new Pages();
			Integer count = jdbc.queryForList(sql1).size();
			pages.setData(list);
			pages.setTotal(count);
			return pages;
	}
	public Object[][] getSalePoundListExport(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time) {
		
			String sql1 = "SELECT \r\n" + 
					"	s.xs_pound_number,s.client,s.materiel,s.traffic_unit,"
					+ "s.wagon_number,s.rough_weight,s.tare,s.net_weight \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			String sql2 = "SELECT \r\n" + 
					" sum(s.rough_weight) as t_rough_weight,sum(s.tare) as t_tare,sum(s.net_weight) as t_net_weight \r\n" +
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			String sqla="";
			if(client != null && !client.isEmpty()){
				sqla += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sqla += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sqla += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sqla += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sqla += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sqla += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sqla += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sqla += " and s.create_time >= '" + start_time + "'";
			}
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				 sqla += " and s.create_time < '" + end + "'";
			}
			System.out.println("（*************sql1列表**************）"+sql1);
			sqla += " order by s.create_time  desc  ";
			System.out.println(sqla+"<!!!!!!!!!!!!!!!!!!!!");
			 String sql=sql1.concat(sqla);//获取数据
			 String sqlb=sql2.concat(sqla);
			List<Map<String, Object>> list = jdbc.queryForList(sql);
			List<Map<String, Object>> countlist= jdbc.queryForList(sqlb);
			Map<String,Object> sa=countlist.get(0);//获取合计数据
			String t_rough_weight=String.valueOf(sa.get("t_rough_weight"));
			String t_tare=String.valueOf(sa.get("t_tare"));
			String t_net_weight=String.valueOf(sa.get("t_net_weight"));
			
			String []one= {"合计"," "," "," "," ",t_rough_weight,t_tare,t_net_weight," "," "};
			System.out.println("((((((((((((输出one[])))))))))"+Arrays.toString(one));
			int size = list.size();
	        Object[][] array = new Object[size][10];
	        String[] arr={"销售磅单号","客户","物料","车号","运输单位","毛重","皮重","净重","轻车时间","重车时间"};
	        //表头 一维数组转进二维
	        int n = 10;//列
	        int m = arr.length/ n ;//行
	        Object [][] b = new Object[m][n];
	        int num = -1;
	        for (int i = 0; i < m; i++) {
	        for (int j = 0; j < n; j++) {
	        num++;
	        b[i][j] = arr[num];
	        }
	        }
	        System.out.println("((((((((((((输出arr[])))))))))"+Arrays.toString(arr));
	        System.out.println("((((((((((((输出b[][])))))))))"+Arrays.toString(b));
	        for (int i =0; i < size; i++) {//循环遍历所有行
	            array[i] = list.get(i).values().toArray();//每行的列数
	        }
	        int x = 10;//列
	        int y = one.length/ x;//行
	        Object [][] c = new Object[y][x];
	        num = -1;
	        for (int i = 0; i < y; i++) {
	        for (int j = 0; j < x; j++) {
	        num++;
	        c[i][j] = one[num];
	        }
	        }
	       //合并数组
	        Object[][] strs3 = Array.unite(b, array);
	        Object[][] total = Array.unite(strs3, c);
	        return total;
	}
	//销售逐车明细报表
	public void getSalePoundListExports(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,HttpServletResponse response) {
		
			String sql1 = "SELECT \r\n" + 
					"	s.xs_pound_number,s.client,s.materiel,s.traffic_unit,"
					+ "s.wagon_number,s.rough_weight,s.tare,s.net_weight,s.light_car_time,s.reconditioning_time \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			String sql2 = "SELECT \r\n" + 
					"	\"合计\" as  xs_pound_number,\"\" as client,\"\" as materiel,\"\" as traffic_unit, \"\" as wagon_number, sum(s.rough_weight) as rough_weight,sum(s.tare) "
					+ "as tare,sum(s.net_weight) as net_weight ,\"\" as light_car_time,\"\" as reconditioning_time \r\n" +
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			String sqla="";
			if(client != null && !client.isEmpty()){
				sqla += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sqla += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sqla += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sqla += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sqla += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sqla += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sqla += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sqla += " and s.create_time >= '" + start_time + "'";
			}
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				 sqla += " and s.create_time < '" + end + "'";
			}
			System.out.println("（*************sql1列表**************）"+sql1);
			sqla += " order by s.create_time  desc  ";
			System.out.println(sqla+"<!!!!!!!!!!!!!!!!!!!!");
			 String sql=sql1.concat(sqla);//获取数据
			 String sqlb=sql2.concat(sqla);
			List<Map<String, Object>> list = jdbc.queryForList(sql);
			Map<String, Object> count= jdbc.queryForMap(sqlb);
			list.add(count);
			/*String t_rough_weight=String.valueOf(sa.get("t_rough_weight"));
			String t_tare=String.valueOf(sa.get("t_tare"));
			String t_net_weight=String.valueOf(sa.get("t_net_weight"));
			
			String []one= {"合计"," "," "," "," ",t_rough_weight,t_tare,t_net_weight," "," "};
			System.out.println("((((((((((((输出one[])))))))))"+Arrays.toString(one));
			int size = list.size();
	        Object[][] array = new Object[size][10];*/
	        String[] arr={"销售磅单号","客户","物料","车号","运输单位","毛重","皮重","净重","轻车时间","重车时间"};
	      //1.创建excel
			HSSFWorkbook wk = new HSSFWorkbook();
			CellStyle cs = wk.createCellStyle();
			CellStyle cs1 = wk.createCellStyle();
			cs.setBorderLeft(CellStyle.BORDER_THIN);
	        cs.setBorderRight(CellStyle.BORDER_THIN);
	        cs.setBorderTop(CellStyle.BORDER_THIN);
	        cs.setBorderBottom(CellStyle.BORDER_THIN);
	        cs.setAlignment(CellStyle.ALIGN_CENTER);
	        cs1.setAlignment(CellStyle.ALIGN_CENTER);
			//2.创建一张工作表
			HSSFSheet sheet = wk.createSheet();
			sheet.setColumnWidth(0, 256*20+150);
			sheet.setColumnWidth(1, 256*18+150);
			sheet.setColumnWidth(2, 256*12+150);
			sheet.setColumnWidth(3, 256*12+150);
			sheet.setColumnWidth(4, 256*18+150);
			sheet.setColumnWidth(5, 256*12+150);
			sheet.setColumnWidth(6, 256*12+150);
			sheet.setColumnWidth(7, 256*12+150);
			sheet.setColumnWidth(8, 256*18+150);
			sheet.setColumnWidth(9, 256*18+150);
			
			sheet.setDefaultRowHeightInPoints(50*50);
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张A5_PAPERSIZE  A4_PAPERSIZE
			sheet.setDisplayGridlines(false);
			sheet.setPrintGridlines(false);
			sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
			sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
			sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
			sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2 ); // 右边距
			//3.设置工作表的列宽
			//sheet.setColumnWidth(0, 5000);
			//4.设置excel表头（创建第一行）行.列
			HSSFRow row0 = sheet.createRow(0);
			HSSFRow rowa = sheet.createRow(1);
			HSSFRow rowb = sheet.createRow(2);
			row0.setHeightInPoints(20);//目的是想把行高设置成20px
			HSSFRow row = sheet.createRow(3);
			HSSFCell cell0 = row0.createCell(0);
			HSSFCell cella = rowa.createCell(0);
			HSSFCell cellb = rowb.createCell(0);
			HSSFCell cell = row.createCell(0);
			
			// 设置字体
			CellStyle redStyle = wk.createCellStyle();
			//居中
			redStyle.setAlignment(CellStyle.ALIGN_CENTER);
			cs1.setAlignment(CellStyle.ALIGN_LEFT);
			
			cell0.setCellStyle(redStyle);
			cella.setCellStyle(cs1);
			cellb.setCellStyle(cs1);
			HSSFFont font2 = wk.createFont();
			//字体
			//redFont.setFontName("宋体");
			//redStyle.setFontName("黑体");
			font2.setColor(Font.COLOR_RED);//红色
			font2.setFontName("仿宋_GB2312");
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//设置字体大小
			font2.setFontHeightInPoints((short) 17);

			redStyle.setFont(font2);//选择需要用到的字体格式
			//cell0.setCellStyle(cs);
			
			//字段样式---》
			// 设置字体
			CellStyle css = wk.createCellStyle();

			CellStyle redStyle2 = wk.createCellStyle();
			//居中
			redStyle2.setAlignment(CellStyle.ALIGN_CENTER);
			redStyle2.setBorderLeft(CellStyle.BORDER_THIN);
			redStyle2.setBorderRight(CellStyle.BORDER_THIN);
			redStyle2.setBorderTop(CellStyle.BORDER_THIN);
			redStyle2.setBorderBottom(CellStyle.BORDER_THIN);
			HSSFFont font3 = wk.createFont();
			//字体
			font3.setColor(Font.BOLDWEIGHT_BOLD);//红色
			//font3.setFontName("仿宋_GB2312");黑体
			//font3.setFontName("黑体");
			font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//设置字体大小
			font3.setFontHeightInPoints((short) 13);

			redStyle2.setFont(font3);//选择需要用到的字体格式
			//————————————————————————————
			String s_time="2019-03-28 09:27:57";
			String e_time="2019-06-28 09:27:57";
			String company="复兴煤业有限公司";
			cell0.setCellValue("山西复兴煤业有限公司销售逐车明细报表");
			cella.setCellValue("日期："+start_time+"至"+end_time);
			cellb.setCellValue("条件："+company);
			cell = row.createCell(0);
			//cell.setCellStyle(cs);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("销售磅单号");
			
			cell = row.createCell(1);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("客户");
			
			cell = row.createCell(2);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("物料");
			
			cell = row.createCell(3);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("车号");
			
			cell = row.createCell(4);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("运输单位");
			
			cell = row.createCell(5);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("毛重");
			
			cell = row.createCell(6);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("皮重");
			
			cell = row.createCell(7);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("净重");
			
			cell = row.createCell(8);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("轻车时间");
			
			cell = row.createCell(9);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("重车时间");
			
			//5.设置excel导出数据
			 // 创建第一行
	        for (short i=0;i<list.size();i++)
	        {
	            row = sheet.createRow(i+4);
	            Cell cell1 = row.createCell(0);
	            Cell cell2 = row.createCell(1);
	            Cell cell3 = row.createCell(2);
	            Cell cell4 = row.createCell(3);
	            Cell cell5 = row.createCell(4);
	            Cell cell6 = row.createCell(5);
	            Cell cell7 = row.createCell(6);
	            Cell cell8 = row.createCell(7);
	            Cell cell9 = row.createCell(8);
	           Cell cell10 = row.createCell(9);
	              cell1.setCellValue(String.valueOf( list.get(i).get("xs_pound_number")));
	              cell1.setCellStyle(cs);
	              
	              cell2.setCellValue(String.valueOf(list.get(i).get("client")));
	              cell2.setCellStyle(cs);
	              
	              cell3.setCellValue(String.valueOf(list.get(i).get("materiel")));
	              cell3.setCellStyle(cs);
	              
	              cell4.setCellValue(String.valueOf(list.get(i).get("wagon_number")));
	              cell4.setCellStyle(cs);
	              
	              cell5.setCellValue(String.valueOf(list.get(i).get("traffic_unit")));
	              cell5.setCellStyle(cs);
	              
	              cell6.setCellValue(String.valueOf(list.get(i).get("rough_weight")));
	              cell6.setCellStyle(cs);
	              
	              cell7.setCellValue(String.valueOf(list.get(i).get("tare")));
	              cell7.setCellStyle(cs);
	              
	              cell8.setCellValue(String.valueOf(list.get(i).get("net_weight")));
	              cell8.setCellStyle(cs);
	              
	              cell9.setCellValue(String.valueOf(list.get(i).get("light_car_time")));
	              cell9.setCellStyle(cs);
	              cell10.setCellValue(String.valueOf(list.get(i).get("reconditioning_time")));
	              cell10.setCellStyle(cs);
	              
	             
	          }
	     
	          CellRangeAddress  range= new CellRangeAddress(0,0,0,9);
	          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$J$2"));
	          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$J$3"));  
	          sheet.addMergedRegion(range);
	  		//6.设置excel统计数据
	          try {
	  			String fileName="销售逐车明细报表";
	        	  response.setContentType("application/vnd.ms-excel");
	        	  response.setCharacterEncoding("utf-8");
	          response.setHeader("content-disposition",
	                   "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+ ".xls");
	  			
	  			response.flushBuffer();
	  			wk.write(response.getOutputStream());
	  			wk.write(new FileOutputStream(new File("D://daochu/销售逐车明细.xls")));
	  		} catch (FileNotFoundException e) {
	  			e.printStackTrace();
	  		} catch (IOException e) {
	  			e.printStackTrace();
	  		}
	}
	public  Object[][] getSaleMatterCountExport(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time) {
		String prefix="select sum(p.vehicle_number) as c_vehicle_number,sum(p.t_rough_weight) as c_rough_weight,sum(p.t_tare) as c_tare,sum(p.t_net_weight) as c_net_weight,sum(p.t_loss) as c_loss from (";
			String sql1 = "SELECT s.client,s.materiel,COUNT(materiel) AS vehicle_number,"
					+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'";
			}
			sql1+=" group by s.materiel  \r\n";
			sql1 += " order by s.create_time  desc  ";
			String Suffix=")p";
			String sqla=prefix.concat(sql1);
			String sql=sqla.concat(Suffix);
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			System.out.println(sql+"<!!!!!!!!!![[*sql]]!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			List<Map<String, Object>> list1 = jdbc.queryForList(sql);
			Map<String,Object> sa=list1.get(0);//获取合计数据
			String vehicle_number=String.valueOf(sa.get("c_vehicle_number"));
			String c_rough_weight=String.valueOf(sa.get("c_rough_weight"));
			String c_tare=String.valueOf(sa.get("c_tare"));
			String c_net_weight=String.valueOf(sa.get("c_net_weight"));
			String c_loss=String.valueOf(sa.get("c_loss"));
			
			String []one= {"合计"," ",vehicle_number,c_rough_weight,c_tare,c_net_weight,c_loss};
			System.out.println("(-------输出one[]数组0------------)"+Arrays.toString(one));//输出数组
			int size = list.size();
	        Object[][] array = new Object[size][7];//数据二维数组
	        String[] arr={"客户","物料","发货单位","车数","皮重","净重","亏损"};
			 //表头 一维数组转进二维
		        int n = 7;//列
		        int m = arr.length/ n ;//行
		        Object [][] b = new Object[m][n];
		        int num = -1;
		        for (int i = 0; i < m; i++) {
		        for (int j = 0; j < n; j++) {
		        num++;
		        b[i][j] = arr[num];
		        }
		        }
		        System.out.println("-------输出arr[]数组1------------"+Arrays.toString(arr));
		        for (int i =0; i < size; i++) {//循环遍历所有行
		            array[i] = list.get(i).values().toArray();//每行的列数
		        }
		        //合并数组
		        Object[][] strs3 = Array.unite(b, array);
		        
		        //合计数据一维转二维
		        int x = 7;//列
		        int y = one.length/ x;//行
		        Object [][] c = new Object[y][x];
		        num = -1;
		        for (int i = 0; i < y; i++) {
		        for (int j = 0; j < x; j++) {
		        num++;
		        c[i][j] = one[num];
		        }
		        }
		        //合并统计数组
		        Object[][] total = Array.unite(strs3, c);
			return total;
	}
	//新销售物料统计
	public void getSaleMatterCountExports(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,HttpServletResponse response) {
		String prefix="select  sum(p.vehicle_number) as c_vehicle_number,sum(p.t_rough_weight) as c_rough_weight,sum(p.t_tare) as c_tare,sum(p.t_net_weight) as c_net_weight,sum(p.t_loss) as c_loss from (";
		//String prefix="select \"合计\" as  client,\"\" as materiel , sum(p.vehicle_number) as c_vehicle_number,sum(p.t_rough_weight) as c_rough_weight,sum(p.t_tare) as c_tare,sum(p.t_net_weight) as c_net_weight,sum(p.t_loss) as c_loss from (";

		String sql1 = "SELECT s.client,s.materiel,COUNT(materiel) AS vehicle_number,"
					+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss \r\n" + 
					"FROM \r\n" + 
					"	db_business_sales_pound_list s \r\n" + 
					"WHERE   1 = 1 ";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'";
			}
			sql1+=" group by s.materiel  \r\n";
			sql1 += " order by s.create_time  desc  ";
			String Suffix=")p";
			String sqla=prefix.concat(sql1);
			String sql=sqla.concat(Suffix);
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			System.out.println(sql+"<!!!!!!!!!![[*sql]]!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			Map<String, Object> count = jdbc.queryForMap(sql);
	        //String[] arr={"客户","物料","发货单位","车数","皮重","净重","亏损"};
	        Map<String,Object> counts=new HashMap();
	       // String prefi="select \"合计\" as  client,\"\" as materiel ,\"\" as materiel , sum(p.vehicle_number) as c_vehicle_number,sum(p.t_rough_weight) as c_rough_weight,sum(p.t_tare) as c_tare,sum(p.t_net_weight) as c_net_weight,sum(p.t_loss) as c_loss from (";
			String vehicle_number=String.valueOf(count.get("c_vehicle_number"));
			String  t_rough_weight=String.valueOf(count.get("c_rough_weight"));
			String t_tare=String.valueOf(count.get("c_tare"));
			String t_net_weight=String.valueOf(count.get("c_net_weight"));
			String t_loss=String.valueOf(count.get("c_loss"));
			//"SELECT s.client,s.materiel,COUNT(materiel) AS vehicle_number,"
			//+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
			//+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss \r\n" + 
			counts.put("client","合计");
			counts.put("materiel"," ");
			counts.put("vehicle_number",vehicle_number);
			counts.put("t_rough_weight",t_rough_weight);
			counts.put("t_tare",t_tare);
			counts.put("t_net_weight",t_net_weight);
			counts.put("t_loss",t_loss);
			list.add(counts);
	      //1.创建excel
			HSSFWorkbook wk = new HSSFWorkbook();
			CellStyle cs = wk.createCellStyle();
			CellStyle cs1 = wk.createCellStyle();
			cs.setBorderLeft(CellStyle.BORDER_THIN);
	        cs.setBorderRight(CellStyle.BORDER_THIN);
	        cs.setBorderTop(CellStyle.BORDER_THIN);
	        cs.setBorderBottom(CellStyle.BORDER_THIN);
	        cs.setAlignment(CellStyle.ALIGN_CENTER);
	        cs1.setAlignment(CellStyle.ALIGN_CENTER);
			//2.创建一张工作表
			HSSFSheet sheet = wk.createSheet();
			sheet.setColumnWidth(0, 256*20+150);
			sheet.setColumnWidth(1, 256*18+150);
			sheet.setColumnWidth(2, 256*12+150);
			sheet.setColumnWidth(3, 256*12+150);
			sheet.setColumnWidth(4, 256*18+150);
			sheet.setColumnWidth(5, 256*12+150);
			sheet.setColumnWidth(6, 256*12+150);
			//sheet.setColumnWidth(7, 256*12+150);
			
			sheet.setDefaultRowHeightInPoints(50*50);
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张A5_PAPERSIZE  A4_PAPERSIZE
			sheet.setDisplayGridlines(false);
			sheet.setPrintGridlines(false);
			sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
			sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
			sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
			sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2 ); // 右边距
			//3.设置工作表的列宽
			//sheet.setColumnWidth(0, 5000);
			//4.设置excel表头（创建第一行）行.列
			HSSFRow row0 = sheet.createRow(0);
			HSSFRow rowa = sheet.createRow(1);
			HSSFRow rowb = sheet.createRow(2);
			row0.setHeightInPoints(20);//目的是想把行高设置成20px
			HSSFRow row = sheet.createRow(3);
			HSSFCell cell0 = row0.createCell(0);
			HSSFCell cella = rowa.createCell(0);
			HSSFCell cellb = rowb.createCell(0);
			HSSFCell cell = row.createCell(0);
			
			// 设置字体
			CellStyle redStyle = wk.createCellStyle();
			//颜色HSSFColor 颜色索引对照表https://blog.csdn.net/for_china2012/article/details/29844661
			//居中
			redStyle.setAlignment(CellStyle.ALIGN_CENTER);
			cs1.setAlignment(CellStyle.ALIGN_LEFT);
			
			cell0.setCellStyle(redStyle);
			cella.setCellStyle(cs1);
			cellb.setCellStyle(cs1);
			HSSFFont font2 = wk.createFont();
			//字体
			//redFont.setFontName("宋体");
			//redStyle.setFontName("黑体");
			font2.setColor(Font.COLOR_RED);//红色
			font2.setFontName("仿宋_GB2312");
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//设置字体大小
			font2.setFontHeightInPoints((short) 17);

			redStyle.setFont(font2);//选择需要用到的字体格式
			//cell0.setCellStyle(cs);
			
			//字段样式---》
			// 设置字体
			CellStyle css = wk.createCellStyle();

			CellStyle redStyle2 = wk.createCellStyle();
			//居中
			redStyle2.setAlignment(CellStyle.ALIGN_CENTER);
			redStyle2.setBorderLeft(CellStyle.BORDER_THIN);
			redStyle2.setBorderRight(CellStyle.BORDER_THIN);
			redStyle2.setBorderTop(CellStyle.BORDER_THIN);
			redStyle2.setBorderBottom(CellStyle.BORDER_THIN);
			HSSFFont font3 = wk.createFont();
			//字体
			font3.setColor(Font.BOLDWEIGHT_BOLD);//红色
			//font3.setFontName("仿宋_GB2312");黑体
			//font3.setFontName("黑体");
			font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//设置字体大小
			font3.setFontHeightInPoints((short) 13);

			redStyle2.setFont(font3);//选择需要用到的字体格式
			//————————————————————————————
			String s_time="2019-03-28 09:27:57";
			String e_time="2019-06-28 09:27:57";
			String company="复兴煤业有限公司";
			cell0.setCellValue("山西复兴煤业有限公司销售物料统计报表");
			cella.setCellValue("日期："+start_time+"至"+end_time);
			cellb.setCellValue("条件："+company);
		//   String[] arr={"供应商","物料","车数","原发净重","毛重","皮重","净重","亏损"};
			//String[] arr={"客户","物料","发货单位","车数","皮重","净重","亏损"};
			cell = row.createCell(0);
			//cell.setCellStyle(cs);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("客户");
			
			cell = row.createCell(1);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("物料");
			
			cell = row.createCell(2);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("车数");
			
			cell = row.createCell(3);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("毛重");
			
			cell = row.createCell(4);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("皮重");
			
			cell = row.createCell(5);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("计量净重");
			
			cell = row.createCell(6);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("亏损");
			
		
			
			
			//5.设置excel导出数据
			 // 创建第一行
	        for (short i=0;i<list.size();i++)
	        {
	            row = sheet.createRow(i+4);
	            Cell cell1 = row.createCell(0);
	            Cell cell2 = row.createCell(1);
	            Cell cell3 = row.createCell(2);
	            Cell cell4 = row.createCell(3);
	            Cell cell5 = row.createCell(4);
	            Cell cell6 = row.createCell(5);
	            Cell cell7 = row.createCell(6);
	          //  Cell cell8 = row.createCell(7);
	           // SELECT s.client,s.materiel,COUNT(materiel) AS vehicle_number,"
					//	+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
				//		+ "sum(net_weight) AS t_net_weight,sum(loss) AS t_loss \r\n" + 
	              cell1.setCellValue(String.valueOf( list.get(i).get("client")));
	              cell1.setCellStyle(cs);
	              
	              cell2.setCellValue(String.valueOf(list.get(i).get("materiel")));
	              cell2.setCellStyle(cs);
	              
	              cell3.setCellValue(String.valueOf(list.get(i).get("vehicle_number")));
	              cell3.setCellStyle(cs);
	              
	              cell4.setCellValue(String.valueOf(list.get(i).get("t_rough_weight")));
	              cell4.setCellStyle(cs);
	              
	              cell5.setCellValue(String.valueOf(list.get(i).get("t_tare")));
	              cell5.setCellStyle(cs);
	              
	              cell6.setCellValue(String.valueOf(list.get(i).get("t_net_weight")));
	              cell6.setCellStyle(cs);
	              
	              cell7.setCellValue(String.valueOf(list.get(i).get("t_loss")));
	              cell7.setCellStyle(cs);
	          }
	     
	          CellRangeAddress  range= new CellRangeAddress(0,0,0,6);
	          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$G$2"));
	          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$G$3"));  
	          sheet.addMergedRegion(range);
	  		//6.设置excel统计数据
	          try {
	  			//response.setContentType("application/octet-stream");
	  			//response.setHeader("Content-disposition", "attachment;filename=销售物料统计报表.xls");//默认Excel名称
	        	  String fileName="销售物料统计报表";
	        	  response.setContentType("application/vnd.ms-excel");
	        	  response.setCharacterEncoding("utf-8");
	          response.setHeader("content-disposition",
	                     "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+ ".xls");
	  			response.flushBuffer();
	  			wk.write(response.getOutputStream());
	  			wk.write(new FileOutputStream(new File("D://daochu/销售物料统计.xls")));
	  		} catch (FileNotFoundException e) {
	  			e.printStackTrace();
	  		} catch (IOException e) {
	  			e.printStackTrace();
	  		}
	        
	}
	public Object[][] getSaleMarginCountExport(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time) {//sum(p.t_rough_weight) as c_rough_weight,sum(p.t_tare) as c_tare,sum(p.t_net_weight) as c_net_weight,sum(p.t_loss) as c_loss,
		String prefix="select sum(p.vehicle_number) as c_vehicle_number,sum(p.t_net_weight) as c_net_weight, sum(p.t_contr_margin) as c_contr_margin from (";
			String sql1 = "SELECT s.client,s.materiel,s.shipping_unit ,COUNT(client) AS vehicle_number,"
					//+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight ,n.contr_margin as t_contr_margin \r\n" + 
					"FROM \r\n" + //,sum(loss) AS t_loss,
					"	db_business_sales_pound_list s ,(SELECT o.customer,o.contr_margin FROM business_sale_order_manage  o where 1=1 GROUP BY o.customer) as n \r\n" + 
					"WHERE   1 = 1 and s.client=n.customer";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'\r\n";
			}
			sql1+="  group by s.client \r\n";
			sql1 += " order by s.create_time  desc  ";
			String Suffix=")p";
			String sqla=prefix.concat(sql1);
			String sql=sqla.concat(Suffix);
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			List<Map<String, Object>> list1 = jdbc.queryForList(sql);
			Map<String,Object> sa=list1.get(0);//获取合计数据
			String c_vehicle_number=String.valueOf(sa.get("c_vehicle_number"));
			//String c_rough_weight=String.valueOf(sa.get("c_rough_weight"));
			//String c_tare=String.valueOf(sa.get("c_tare"));
			String c_net_weight=String.valueOf(sa.get("c_net_weight"));
			String c_contr_margin=String.valueOf(sa.get("c_contr_margin"));
			//String c_loss=String.valueOf(sa.get("c_loss"));
			
			String []one= {"合计"," ","",c_vehicle_number,c_net_weight,c_contr_margin};//c_rough_weight,c_tare,
			System.out.println("(-------输出one[]数组0------------)"+Arrays.toString(one));//输出数组
			int size = list.size();
	        Object[][] array = new Object[size][7];//数据二维数组
	        String[] arr={"客户","物料","发货单位","车数","净重吨数","余量吨数"};
			 //表头 一维数组转进二维
		        int n = 6;//列
		        int m = arr.length/ n ;//行
		        Object [][] b = new Object[m][n];
		        int num = -1;
		        for (int i = 0; i < m; i++) {
		        for (int j = 0; j < n; j++) {
		        num++;
		        b[i][j] = arr[num];
		        }
		        }
		        System.out.println("-------输出arr[]数组1------------"+Arrays.toString(arr));
		        for (int i =0; i < size; i++) {//循环遍历所有行
		            array[i] = list.get(i).values().toArray();//每行的列数
		        }
		        //合并数组
		        Object[][] strs3 = Array.unite(b, array);
		        
		        //合计数据一维转二维
		        int x = 6;//列
		        int y = one.length/ x;//行
		        Object [][] c = new Object[y][x];
		        num = -1;
		        for (int i = 0; i < y; i++) {
		        for (int j = 0; j < x; j++) {
		        num++;
		        c[i][j] = one[num];
		        }
		        }
		        //合并统计数组
		        Object[][] total = Array.unite(strs3, c);
			return total;
	}
	//新销售客户余量报表导出
	public void getSaleMarginCountExports(String client,String materiel,
			String wagonNumber,String trafficUnit,String driver,String matter_type,String shippingUnit,
			String start_time,String end_time,HttpServletResponse response) {//sum(p.t_rough_weight) as c_rough_weight,sum(p.t_tare) as c_tare,sum(p.t_net_weight) as c_net_weight,sum(p.t_loss) as c_loss,
		String prefix="select sum(p.vehicle_number) as c_vehicle_number,sum(p.t_net_weight) as c_net_weight, sum(p.t_contr_margin) as c_contr_margin from (";
			String sql1 = "SELECT s.client,s.materiel,s.shipping_unit ,COUNT(client) AS vehicle_number,"
					//+ "sum(rough_weight) AS t_rough_weight,sum(tare) AS t_tare,"
					+ "sum(net_weight) AS t_net_weight ,n.contr_margin as t_contr_margin \r\n" + 
					"FROM \r\n" + //,sum(loss) AS t_loss,
					"	db_business_sales_pound_list s ,(SELECT o.customer,o.contr_margin FROM business_sale_order_manage  o where 1=1 GROUP BY o.customer) as n \r\n" + 
					"WHERE   1 = 1 and s.client=n.customer";
			if(client != null && !client.isEmpty()){
				sql1 += " and s.client =  '" + client + "'";
			}
			if(materiel != null && !materiel.isEmpty()){
				sql1 += " and s.materiel = '" + materiel + "'";
			}
			if(wagonNumber != null && !wagonNumber.isEmpty()){
				sql1 += " and s.wagon_number =  '" + wagonNumber + "'";
			}
			if(trafficUnit != null && !trafficUnit.isEmpty()){
				sql1 += " and s.traffic_unit =  '" + trafficUnit + "'";
			}
			if(driver != null && !driver.isEmpty()){
				sql1 += " and s.driver =  '" + driver + "'";
			}
			if(matter_type != null && !matter_type.isEmpty()){
				sql1 += " and s.matter_type =  '" + matter_type + "'";
			}
			if(shippingUnit != null && !shippingUnit.isEmpty()){
				sql1 += " and s.shipping_unit =  '" + shippingUnit + "'";
			}
			if(start_time != null && !start_time.isEmpty()){
				sql1 += " and s.create_time >= '" + start_time + "'";
			}
			
			if(end_time != null && !end_time.isEmpty()) {
				 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
				 String end=CommonsUtil.timeToString(cTime);
				sql1 += " and s.create_time < '" + end + "'\r\n";
			}
			sql1+="  group by s.client \r\n";
			sql1 += " order by s.create_time  desc  ";
			String Suffix=")p";
			String sqla=prefix.concat(sql1);
			String sql=sqla.concat(Suffix);
			System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
			List<Map<String, Object>> list = jdbc.queryForList(sql1);
			Map<String, Object>count = jdbc.queryForMap(sql);
			/*Map<String,Object> sa=list1.get(0);//获取合计数据
			String c_vehicle_number=String.valueOf(sa.get("c_vehicle_number"));
			//String c_rough_weight=String.valueOf(sa.get("c_rough_weight"));
			//String c_tare=String.valueOf(sa.get("c_tare"));
			String c_net_weight=String.valueOf(sa.get("c_net_weight"));
			String c_contr_margin=String.valueOf(sa.get("c_contr_margin"));
			//String c_loss=String.valueOf(sa.get("c_loss"));
			*/
			//String []one= {"合计"," ","",c_vehicle_number,c_net_weight,c_contr_margin};//c_rough_weight,c_tare,
	        String[] arr={"客户","物料","发货单位","车数","净重吨数","余量吨数"};
			//String prefix="select sum(p.vehicle_number) as c_vehicle_number,sum(p.t_net_weight) as c_net_weight, sum(p.t_contr_margin) as c_contr_margin from (";

	        Map<String,Object> counts=new HashMap();
			String vehicle_number=String.valueOf(count.get("c_vehicle_number"));
			String t_net_weight=String.valueOf(count.get("c_net_weight"));
			//String t_rough_weight=String.valueOf(count.get("c_tare"));
			//String t_tare=String.valueOf(count.get("c_net_weight"));
			String t_contr_margin=String.valueOf(count.get("c_contr_margin"));
			//String t_loss=String.valueOf(count.get("c_loss"));
			// s.client,s.materiel,s.shipping_unit ,COUNT(client) AS vehicle_number,"
			//+ "sum(net_weight) AS t_net_weight ,n.contr_margin as t_contr_margin \
			counts.put("client","合计");
			counts.put("materiel"," ");
			counts.put("shipping_unit"," ");
			counts.put("vehicle_number",vehicle_number);
			counts.put("t_net_weight",t_net_weight);
			counts.put("t_contr_margin",t_contr_margin);
			list.add(counts);
	      //1.创建excel
			HSSFWorkbook wk = new HSSFWorkbook();
			CellStyle cs = wk.createCellStyle();
			CellStyle cs1 = wk.createCellStyle();
			cs.setBorderLeft(CellStyle.BORDER_THIN);
	        cs.setBorderRight(CellStyle.BORDER_THIN);
	        cs.setBorderTop(CellStyle.BORDER_THIN);
	        cs.setBorderBottom(CellStyle.BORDER_THIN);
	        cs.setAlignment(CellStyle.ALIGN_CENTER);
	        cs1.setAlignment(CellStyle.ALIGN_CENTER);
			//2.创建一张工作表
			HSSFSheet sheet = wk.createSheet();
			sheet.setColumnWidth(0, 256*20+150);
			sheet.setColumnWidth(1, 256*12+150);
			sheet.setColumnWidth(2, 256*20+150);
			sheet.setColumnWidth(3, 256*12+150);
			sheet.setColumnWidth(4, 256*18+150);
			sheet.setColumnWidth(5, 256*12+150);
			
			sheet.setDefaultRowHeightInPoints(50*50);
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张A5_PAPERSIZE  A4_PAPERSIZE
			sheet.setDisplayGridlines(false);
			sheet.setPrintGridlines(false);
			sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
			sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
			sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
			sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2 ); // 右边距
			//3.设置工作表的列宽
			//sheet.setColumnWidth(0, 5000);
			//4.设置excel表头（创建第一行）行.列
			HSSFRow row0 = sheet.createRow(0);
			HSSFRow rowa = sheet.createRow(1);
			HSSFRow rowb = sheet.createRow(2);
			row0.setHeightInPoints(20);//目的是想把行高设置成20px
			HSSFRow row = sheet.createRow(3);
			HSSFCell cell0 = row0.createCell(0);
			HSSFCell cella = rowa.createCell(0);
			HSSFCell cellb = rowb.createCell(0);
			HSSFCell cell = row.createCell(0);
			
			// 设置字体
			CellStyle redStyle = wk.createCellStyle();
			//颜色HSSFColor 颜色索引对照表https://blog.csdn.net/for_china2012/article/details/29844661
			//居中
			redStyle.setAlignment(CellStyle.ALIGN_CENTER);
			cs1.setAlignment(CellStyle.ALIGN_LEFT);
			
			cell0.setCellStyle(redStyle);
			cella.setCellStyle(cs1);
			cellb.setCellStyle(cs1);
			HSSFFont font2 = wk.createFont();
			//字体
			font2.setColor(Font.COLOR_RED);//红色
			font2.setFontName("仿宋_GB2312");
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//设置字体大小
			font2.setFontHeightInPoints((short) 17);

			redStyle.setFont(font2);//选择需要用到的字体格式
			//cell0.setCellStyle(cs);
			
			//字段样式---》
			// 设置字体
			CellStyle css = wk.createCellStyle();

			CellStyle redStyle2 = wk.createCellStyle();
			//居中
			redStyle2.setAlignment(CellStyle.ALIGN_CENTER);
			redStyle2.setBorderLeft(CellStyle.BORDER_THIN);
			redStyle2.setBorderRight(CellStyle.BORDER_THIN);
			redStyle2.setBorderTop(CellStyle.BORDER_THIN);
			redStyle2.setBorderBottom(CellStyle.BORDER_THIN);
			HSSFFont font3 = wk.createFont();
			//字体
			font3.setColor(Font.BOLDWEIGHT_BOLD);//红色
			font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//设置字体大小
			font3.setFontHeightInPoints((short) 13);

			redStyle2.setFont(font3);//选择需要用到的字体格式
			//————————————————————————————
			String s_time="2019-03-28 09:27:57";
			String e_time="2019-06-28 09:27:57";
			String company="复兴煤业有限公司";
			cell0.setCellValue("山西复兴煤业有限公司销售客户余量报表");
			cella.setCellValue("日期："+start_time+"至"+end_time);
			cellb.setCellValue("条件："+company);
		//   String[] arr={"供应商","物料","车数","原发净重","毛重","皮重","净重","亏损"};
		    //String[] arr={"客户","物料","发货单位","车数","净重吨数","余量吨数"};
			cell = row.createCell(0);
			//cell.setCellStyle(cs);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("客户");
			
			cell = row.createCell(1);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("物料");
			
			cell = row.createCell(2);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("发货单位");
			
			cell = row.createCell(3);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("车数");
			
			cell = row.createCell(4);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("净重吨数");
			
			cell = row.createCell(5);
			cell.setCellStyle(redStyle2);
			cell.setCellValue("余量吨数");
			
			
			//5.设置excel导出数据
			 // 创建第一行
	        for (short i=0;i<list.size();i++)
	        {
	            row = sheet.createRow(i+4);
	            Cell cell1 = row.createCell(0);
	            Cell cell2 = row.createCell(1);
	            Cell cell3 = row.createCell(2);
	            Cell cell4 = row.createCell(3);
	            Cell cell5 = row.createCell(4);
	            Cell cell6 = row.createCell(5);
	              cell1.setCellValue(String.valueOf( list.get(i).get("client")));
	              cell1.setCellStyle(cs);
	              
	              cell2.setCellValue(String.valueOf(list.get(i).get("materiel")));
	              cell2.setCellStyle(cs);
	              
	              cell3.setCellValue(String.valueOf(list.get(i).get("shipping_unit")));
	              cell3.setCellStyle(cs);
	              
	              cell4.setCellValue(String.valueOf(list.get(i).get("vehicle_number")));
	              cell4.setCellStyle(cs);
	              
	              cell5.setCellValue(String.valueOf(list.get(i).get("t_net_weight")));
	              cell5.setCellStyle(cs);
	              
	              cell6.setCellValue(String.valueOf(list.get(i).get("t_contr_margin")));
	              cell6.setCellStyle(cs);
	              
	              
	          }
	     
	          CellRangeAddress  range= new CellRangeAddress(0,0,0,5);
	          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$F$2"));
	          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$F$3"));  
	          sheet.addMergedRegion(range);
	  		//6.设置excel统计数据
	          try {
	        	  String fileName="销售客户余量报表";
	        	  response.setContentType("application/vnd.ms-excel");
	        	  response.setCharacterEncoding("utf-8");
	          response.setHeader("content-disposition",
	                     "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+ ".xls");
	        	  response.flushBuffer();
	  			wk.write(response.getOutputStream());
	  			wk.write(new FileOutputStream(new File("D://daochu/客户余量报表.xls")));
	  		} catch (FileNotFoundException e) {
	  			e.printStackTrace();
	  		} catch (IOException e) {
	  			e.printStackTrace();
	  		}
	        

	}
}
